package dbops;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.eclipse.hyades.execution.runtime.datapool.IDatapoolIterator;

import com.ibm.jvm.dtfjview.commands.SetCommand;

import core.exceptions.ResultFailureException;
import core.layout.Layout;

import dbops.COLUMN.DATA_TYPE;

/**
 * Description : Functional Test Script
 * 
 * @author angel.tsvetkov
 */
public class SQLOperator extends DatapoolOperator {

	protected String name; // Table name
	protected String schema; // Schema name
	protected DBConnection connection = new DBConnection();

	/**
	 * Execute the query which is passed as argument
	 * 
	 * @param query
	 *            query which will be executed.
	 * 
	 *            info: The connection must be opened
	 */
	public int executeStatement(String query) throws SQLException {
		int updated = 0;
		Statement st = null;;
		try
		{
			connection.openConnection();
			st = connection.getConnection().createStatement();
			updated = st.executeUpdate(query);
		}catch (SQLException e) {
			setErrorComment("Problem appear during statement execution!",e);
		}
		finally
		{
			if(st != null)
			{
				st.close();
			}
			connection.closeConnection();
		}

		return updated;
	}
	
	public ArrayList<ArrayList<String>> executeSelect(String query) throws SQLException {
		ResultSet updated = null;
		ArrayList<ArrayList<String>> res = new ArrayList<ArrayList<String>>();
		Statement st = null;
		try
		{
			connection.openConnection();
			st = connection.getConnection().createStatement();
			updated = st.executeQuery(query);
			
			ResultSetMetaData rsmd = updated.getMetaData();
			int numberOfColumns = rsmd.getColumnCount();

			while (updated.next()) 
			{
				ArrayList<String> row = new ArrayList<String>();
				for(int idx = 1; idx <= numberOfColumns; idx++)
				{
					String element = updated.getString(idx);
					row.add(element);
				}
				res.add(row);
			}
			
		}catch (SQLException e) {
			setErrorComment("Problem appear during statement execution!",e);
		}
		finally
		{
			if(st != null)
			{
				st.close();
			}
			connection.closeConnection();
		}

		return res;
	}
	
	public Layout executeSelect(String query,Layout layout) throws SQLException, ResultFailureException {
		
		ResultSet updated = null;
		Statement st = null;
		try
		{
			connection.openConnection();
			st = connection.getConnection().createStatement();
			updated = st.executeQuery(query);
			
			ResultSetMetaData rsmd = updated.getMetaData();
			int numberOfColumns = rsmd.getColumnCount();

			if (updated.next()) 
			{
				for(int idx = 1; idx <= numberOfColumns; idx++)
				{
					String elementValue = updated.getString(idx);
					String elementName = rsmd.getColumnLabel(idx);
					try
					{
						layout.set(elementName, elementValue.trim());
					}
					catch (ResultFailureException e) {
						System.err.println("The element is not mapped!");
					}
				}
			}
			
		}catch (SQLException e) {
			setErrorComment("Problem appear during statement execution!",e);
		}
		finally
		{
			if(st != null)
			{
				st.close();
			}
			connection.closeConnection();
		}

		return layout;
	}
	
	public ResultSet execute(String query) throws SQLException {
		ResultSet updated = null;
		Statement st = null;;
		try
		{
			connection.openConnection();
			st = connection.getConnection().createStatement();
			System.out.println(query);
			updated = st.executeQuery(query);
			
		}catch (SQLException e) {
			setErrorComment("Problem appear during statement execution!",e);
		}
		finally
		{
			if(st != null)
			{
				st.close();
			}
			connection.closeConnection();
		}

		return updated;
	}

	/**
	 * Execute the query which is passed as argument
	 * 
	 * @throws SQLException
	 *             if an error occurred accessing the database
	 * 
	 *             info: The connection must be opened
	 */
	protected boolean verifyTableExists() throws SQLException {
		int result = 0;

		String query = "SELECT count(*) FROM SYSCAT.TABLES WHERE TABSCHEMA = '"
				+ schema + "' and TABNAME = '" + name + "'";
		Statement st = null;
		ResultSet stResult = null;

		try
		{
			connection.openConnection();
			st = connection.getConnection().createStatement();
			stResult = st.executeQuery(query);
			if (stResult.next()) {
				// Get First column in first row
				// 1 - table exists
				// 0 - table does not exist
				result = Integer.parseInt(stResult.getString("1"));
			}
		}
		catch(SQLException e)
		{
			setErrorComment("Problem appear during reading data!",e);
		}
		finally
		{
			if(stResult != null)
			{
				stResult.close();
			}
			if(st != null)
			{
				st.close();
			}
			connection.closeConnection();
		}

		return (result == 1);
	}

	/**
	 * Return string representation of all elements in the passed array list,
	 * separated by comma
	 * 
	 * @param dataField
	 *            array list which contain string parameters
	 */
	private String formatDataFields(ArrayList<String> dataField) {
		String formatedData = "";

		if(dataField != null)
		{
			for (int column = 0; column < columns.size(); column++) {
				if (columns.get(column).Type() == DATA_TYPE.StringType) {
					formatedData += "'" + dataField.get(column) + "'" + ",";
				} else {
					formatedData += dataField.get(column) + ",";
				}
			}
	
			// remove the last comma
			formatedData = formatedData.substring(0, formatedData.length() - 1);
		}
		return formatedData;
	}

	/**
	 * Return string representation of all elements in the passed cells,
	 * separated by comma
	 * 
	 * @param cells
	 *            array of cells which values have to be formated
	 */
	private String formatDataFields(CELL... cells) {
		String formatedData = "";

		for (CELL cell : cells) {
			if (cell.getType() == DATA_TYPE.StringType) {
				formatedData += "'" + cell.getValue() + "'" + ",";
			} else {
				formatedData += cell.getValue() + ",";
			}
		}

		// remove the last comma
		formatedData = formatedData.substring(0, formatedData.length() - 1);

		return formatedData;
	}

	/**
	 * Return all columns name separated by comma
	 */
	protected String getColumnNames() {
		String columnNames = "";

		for (int column = 0; column < columns.size(); column++) {
			columnNames += columns.get(column).Name() + ",";
		}

		// remove the last comma
		columnNames = columnNames.substring(0, columnNames.length() - 1);

		return columnNames;
	}

	/**
	 * Generate and execute an INSERT query with passed parameters
	 * 
	 * @param dataField
	 *            data which will be populated in the columns
	 * 
	 *            info: The DB connection must be opened
	 * @throws SQLException 
	 */
	protected boolean insertDataRow(ArrayList<String> dataField) throws SQLException {
		boolean result = true;

		if (dataField.size() > 0 && dataField.size() == columns.size()) {
			executeStatement(formatQuery("INSERT INTO  %1$.%2$(%3$) values(%4$)",false, 
					schema,name,getColumnNames(),formatDataFields(dataField)));

		} else {
			result = false;
			System.out.println("TABLE ROW HAS A DIFFERENT COLUMNS COUNT!");
		}

		return result;
	}

	/**
	 * Generate and execute an INSERT query with passed parameters
	 * 
	 * @param cells
	 *            cells which values will be populated in the columns
	 * 
	 *            info: The DB connection must be opened
	 * @throws SQLException 
	 */
	protected boolean insertDataRow(CELL... cells) throws SQLException {
		boolean result = true;
		executeStatement(formatQuery("INSERT INTO  %1$.%2$ values(%3$)",false,
				schema, name, formatDataFields(cells)));
		return result;
	}

	/**
	 * Populate specified data table with all datapool content
	 * 
	 * @param cells
	 *            cells which values will be populated in the columns
	 * @throws SQLException 
	 * 
	 */
	protected void INSERT(String datapoolName) throws SQLException {
		IDatapoolIterator dpIter = loadDatapool(datapoolName);
	    Statement stmt = null;
	    Connection con = null;
	    
		try
		{
			connection.openConnection();
			con = connection.getConnection();
			con.setAutoCommit(false);
			stmt = con.createStatement();
			while (!dpIter.dpDone()) {
				stmt.addBatch(formatQuery("INSERT INTO  %1$.%2$(%3$) values(%4$)",false, 
						schema,name,getColumnNames(),formatDataFields(getDatapoolRow(dpIter))));
				dpIter.dpNext();
			}
			stmt.executeBatch();
			con.commit();
		}
		catch(SQLException e)
		{
			setErrorComment("The problem appear during inserting data in " + schema
					+ "." + name + " !",e);
		}
		finally
		{
			if(stmt != null)
			{
				stmt.close();
			}
			connection.closeConnection();
		}
		setComment("All rows are completely inserted in " + schema + "." + name
				+ " !");
	}

	/**
	 * Create table if it's not exsists
	 */
	protected void CREATE() {
		
		try {
			if (!verifyTableExists()) {
				// Create Table and schema if needed
				System.out.println("Table " + schema + "." + name
						+ " was created successfuly");
				setComment("Table " + schema + "." + name
						+ " was created successfuly");
			}
		} catch (SQLException e) {
			setErrorComment("The problem appear during creation of " + schema
					+ "." + name + " !",e);
		}
		setComment(schema
				+ "." + name + " was created successfully");	
	}

	/**
	 * Compare two DB tables and return the different lines
	 * 
	 * @param firstTable
	 *            first table which will be compared
	 * @param seconfTable
	 *            second table which will be compared
	 * @throws SQLException 
	 * 
	 */
	protected boolean COMPARE(String firstTable, String seconfTable) throws SQLException {
		boolean result = true;
		
		String query = formatQuery("SELECT * FROM %1$ EXCEPT ALL SELECT * FROM %2$ UNION ALL "
		+ "SELECT * FROM %2$ EXCEPT ALL SELECT * FROM %1$",firstTable,seconfTable);

		try {
			 int afectedRows = executeStatement(query);

			if (afectedRows > 0) {
				setErrorComment("There are unexpected differences.");
				result = false;
			}

		} catch (SQLException e) {
			setErrorComment("The problem appear during comparison of " + firstTable
					+ "and" + seconfTable + " !",e);
		}
		
		return result;
	}

	/**
	 * Determine cell type and return formated value ready to be used in query
	 * statement
	 * 
	 * @param cell
	 *            cell which value will be formated
	 * 
	 */
	private String format(CELL cell) {
		String retValue = "";

		if (cell.getType() == DATA_TYPE.StringType) {
			retValue = "'" + cell.getValue() + "'";
		} else {
			retValue = cell.getValue();
		}

		return retValue;
	}

	/**
	 * Select DB table and return its DataTable representation. Possible usage
	 * with filtering by column(s)
	 * 
	 * @param cells
	 *            cell or cells which will be used to extract specify data
	 * @throws SQLException 
	 * 
	 */
	protected DataTable SELECT(int numberOfRecord, CELL... cells) throws SQLException {
		String query = formatQuery("SELECT * FROM %1$.%2$ WHERE 1 = 1",schema,name);
		Statement st = null;
		ResultSet stResult = null;
		int rowCounter = 0;
		DataTable table = new DataTable();

		table.columns = (ArrayList<COLUMN>) getDBColumns();

		// Add where clause
		for (CELL cell : cells) {
			query += " AND " + cell.getColumn().Name() + " = " + format(cell);
		}

		try {
			connection.openConnection();
			st = connection.getConnection().createStatement();
			stResult = st.executeQuery(query);
			System.out.println(stResult.getRow());
			while (stResult.next()) {
				ROW row = new ROW();
				for (int iIdx = 1; iIdx <= table.columns.size(); iIdx++) {
					row.insert(table.columns.get(iIdx - 1).Type(), stResult
							.getString(iIdx));
					//setComment("Insert " + stResult.getString(iIdx) + " in " + table.columns.get(iIdx - 1).Name() + " column of " + schema + "." + name);
				}
				table.rows.add(row);
				rowCounter++;
				if(numberOfRecord != 0 &&  rowCounter == numberOfRecord)
				{
					break;
				}
			}

		} catch (SQLException e) {
			setErrorComment("The problem appear during comparison extracting data !",e);
		}
		finally
		{
			if(stResult != null)
			{
				stResult.close();
			}
			if(st != null)
			{
				st.close();
			}
			connection.closeConnection();
			
		}
		return table;
	}
	
	protected DataTable SELECT(CELL... cells) throws SQLException {
		return SELECT(0, cells);
	}

	/**
	 * Escapes all parameters and then replaces them in aQuery. The parameters
	 * are replaced according their index in the array aParams. The parameters
	 * in aQuery are defined in the format %INDEX$ or '%INDEX$' (for strings),
	 * where INDEX is the index of the parameter in the array aParams. This
	 * method implicitly calls formatQuery(aQuery, aParams, true).
	 * 
	 * @param aQuery
	 * @param aParams
	 * @return Formated query
	 */
	protected static String formatQuery(String aQuery, String... aParams) {
		return formatQuery(aQuery,true, aParams);
	}

	/**
	 * Escapes all parameters and then replaces them in aQuery. The parameters
	 * are replaced according their index in the array aParams. The parameters
	 * in aQuery are defined in the format %INDEX$ or '%INDEX$' (for strings),
	 * where INDEX is the index of the parameter in the array aParams.
	 * 
	 * @param aQuery
	 * @param aParams
	 * @param escapeSingleQuotes
	 *            If true escapes single quotes in the parameters.
	 * @return Formated query
	 */
	private static String formatQuery(String aQuery,
			boolean escapeSingleQuotes,String...aParams) {
		for (int i = 0; i < aParams.length; i++) {
			String param = aParams[i];
			String replacedExpression = null;
			if (param == null) {
				replacedExpression = "'%" + (i + 1) + "\\$'";
				aQuery = aQuery.replaceAll(replacedExpression, "null");
				replacedExpression = "%" + (i + 1) + "\\$";
				aQuery = aQuery.replaceAll(replacedExpression, "null");
				continue;
			}
			replacedExpression = "%" + (i + 1) + "\\$";
			String escapedParam = quoteRegularExpression(escapeSingleQuotes ? escapeQuery(aParams[i])
					: aParams[i]);
			aQuery = aQuery.replaceAll(replacedExpression, escapedParam);
		}
		return aQuery;
	}

	/**
	 * Replaces all single quotes with two single quotes
	 * 
	 * @param parameter
	 * @return
	 */
	private static String escapeQuery(String parameter) {
		return parameter == null ? null : parameter.replaceAll("'", "''");
	}

	private static String quoteRegularExpression(String str) {
		// replace ? + * / . ^ $ as long as they're not in character
		// class. so must be done by hand
		char[] chrs = str.toCharArray();
		int sz = chrs.length;
		StringBuffer buffer = new StringBuffer(2 * sz);
		for (int i = 0; i < sz; i++) {
			switch (chrs[i]) {
			case '[':
			case ']':
			case '?':
			case '+':
			case '*':
			case '/':
			case '.':
			case '^':
			case '$':
			case '\\':
				buffer.append("\\");
				buffer.append(chrs[i]);
				break;
			default:
				buffer.append(chrs[i]);
			}
		}
		return buffer.toString();
	}

	/**
	 * Define all non string DB data types
	 */
	private ArrayList<String> nonStringTypes() {
		ArrayList<String> nonStringTypes = new ArrayList<String>();

		nonStringTypes.add("SMALLINT");
		nonStringTypes.add("INTEGER");
		nonStringTypes.add("BIGINT");
		nonStringTypes.add("REAL");
		nonStringTypes.add("DOUBLE");
		nonStringTypes.add("DECIMAL");
		nonStringTypes.add("DECFLOAT");
		nonStringTypes.add("BLOB");

		return nonStringTypes;
	}

	/**
	 * Convert DB data type to DATA_TYPE
	 */
	protected DATA_TYPE getDataType(String type) {
		DATA_TYPE retType = DATA_TYPE.StringType;

		for (String nonStringType : nonStringTypes()) {
			if (type.contains(nonStringType)) {
				retType = DATA_TYPE.OtherType;
			}
		}
		return retType;
	}

	/**
	 * Return column names in their order in DB table
	 * @throws SQLException 
	 */
	protected List<COLUMN> getDBColumns() throws SQLException {
		List<COLUMN> columnNames = new ArrayList<COLUMN>();

		connection.openConnection();

		ResultSet columnsResultSet = null;
		try {
			DatabaseMetaData metaData = connection.getConnection()
					.getMetaData();

			columnsResultSet = metaData.getColumns(null, schema, name, null);
			while (columnsResultSet.next()) {
				columnNames.add(new COLUMN(columnsResultSet.getString("COLUMN_NAME"), getDataType(columnsResultSet
						.getString("TYPE_NAME"))));
			 System.out.println(columnsResultSet.getString("COLUMN_NAME"));
			}
		} catch (SQLException e) {
			setErrorComment("Number of columns can't be retrived.", e);
		} finally {
			if(columnsResultSet != null)
			{
				columnsResultSet.close();
			}
			connection.closeConnection();
		}

		return columnNames;
	}
}
